﻿namespace Yom.Data
{
    using System;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    /// <summary>
    /// 数据库基础操作类
    /// </summary>
    public class DBHelper
    {
        private readonly string ConnectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;

        #region 私有方法
        /// <summary>
        /// 获取分页SQL语句
        /// 
        /// 创建: 邱柯皓 2009/01/17 18:44
        /// </summary>
        /// <param name="pageSize">每页显示数量</param>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="tblName">表名</param>
        /// <param name="fldName">排序字段名</param>
        /// <param name="condition">查询条件</param>
        /// <param name="sort">排序方式, true为正序, false为倒序</param>
        /// <returns>返回分页SQL语句</returns>
        private string GetPagerSQL(int pageSize, int pageIndex, string tblName, string fldName, string condition, bool sort)
        {
            string strSort = sort ? " ASC" : " DESC";

            if (pageIndex <= 1)
            {
                return "SELECT * FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY " + fldName + " " + strSort + ") AS rowNum FROM " + tblName + ((string.IsNullOrEmpty(condition)) ? string.Empty : (" WHERE " + condition)) + ") AS myTable WHERE rowNum BETWEEN 1 AND " + pageSize.ToString();
            }
            else
            {
                return "SELECT * FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY " + fldName + " " + strSort + ") AS rowNum FROM " + tblName + ((string.IsNullOrEmpty(condition)) ? string.Empty : (" WHERE " + condition)) + ") AS myTable WHERE rowNum BETWEEN " + (((pageIndex - 1) * pageSize) + 1).ToString() + " AND " + (pageSize * pageIndex).ToString();
            }
        }

        /// <summary>
        /// 生成要执行的命令
        /// 
        /// 创建: 邱柯皓 2009/01/17 18:53
        /// </summary>
        /// <param name="conn">数据库链接对象</param>
        /// <param name="cmd">SqlCommand对象</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="trans">事务</param>
        /// <param name="cmdparms">参数</param>
        private void PrepareCommand(SqlConnection conn, SqlCommand cmd, CommandType cmdType, string cmdText, SqlTransaction trans, SqlParameter[] cmdparms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            if (cmdparms != null)
            {
                foreach (SqlParameter parm in cmdparms)
                {
                    parm.ParameterName = parm.ParameterName;
                    cmd.Parameters.Add(parm);
                }
            }
        }

        /// <summary>
        /// 创建SqlParameter实例
        /// 
        /// 创建: 邱柯皓 2009/01/19 10:19
        /// </summary>
        /// <param name="paraName">实例名</param>
        /// <param name="dbType">类型</param>
        /// <param name="size">大小</param>
        /// <param name="value">值</param>
        /// <param name="direction">参数类型</param>
        /// <returns>返回SqlParameter实例</returns>
        private SqlParameter CreateSqlParameter(string paraName, DbType dbType, int size, object value, ParameterDirection direction)
        {
            SqlParameter para = new SqlParameter();
            para.ParameterName = paraName;

            if (size != 0)
                para.Size = size;

            para.DbType = dbType;

            if (value != null)
                para.Value = value;

            para.Direction = direction;

            return para;
        }
        #endregion

        #region 公共方法
        /// <summary>
        /// 执行数据库查询, 返回DataSet
        /// 
        /// 创建: 邱柯皓 2009/01/17 19:06
        /// </summary>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">查询语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>返回DataSet</returns>
        public DataSet ExecuteQuery(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(conn, cmd, cmdType, cmdText, null, cmdParms);

                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        da.Fill(ds, "Table");
                        cmd.Parameters.Clear();

                        return ds;
                    }
                }
            }
        }

        /// <summary>
        /// 在事务中执行查询, 返回DataSet
        /// 
        /// 创建: 邱柯皓 2009/01/17 19:09
        /// </summary>
        /// <param name="trans">事务</param>
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">查询语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>返回DataSet</returns>
        public DataSet ExecuteQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(trans.Connection, cmd, cmdType, cmdText, trans, cmdParms);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds, "Table");
            cmd.Parameters.Clear();
            da.Dispose();

            return ds;
        }

        /// <summary>
        /// 执行数据库查询, 返回查询所影响的行数
        /// 
        /// 创建: 邱柯皓 2009/01/17 19:12
        /// </summary>
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">查询语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>返回查询所影响的行数</returns>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                PrepareCommand(conn, cmd, cmdType, cmdText, null, cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();

                return val;
            }
        }

        /// <summary>
        /// 在事务中执行查询, 返回查询所影响的行数
        /// 
        /// 创建: 邱柯皓 2009/01/17 19:14
        /// </summary>
        /// <param name="trans">事务</param>
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>返回查询所影响的行数</returns>
        public int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(trans.Connection, cmd, cmdType, cmdText, trans, cmdParms);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();

            return val;
        }

        /// <summary>
        /// 执行数据库查询, 返回DataReader
        /// 
        /// 创建: 邱柯皓 2009/01/17 19:16
        /// </summary>
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>返回DataReader</returns>
        public SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(this.ConnectionString);

            try
            {
                PrepareCommand(conn, cmd, cmdType, cmdText, null, cmdParms);
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();

                return dr;
            }
            catch (Exception ex)
            {
                conn.Close();
                throw ex;
            }
        }

        /// <summary>
        /// 在事务中执行查询, 返回DataReader
        /// 
        /// 创建: 邱柯皓 2009/01/17 19:19
        /// </summary>
        /// <param name="trans">事务</param>
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>返回DataReader</returns>
        public SqlDataReader ExecuteReader(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(trans.Connection, cmd, cmdType, cmdText, trans, cmdParms);
            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();

            return dr;
        }

        /// <summary>
        /// 执行数据库查询, 返回结果第一行第一列
        /// 
        /// 创建: 邱柯皓 2009/01/17 19:22
        /// </summary>
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>返回结果第一行第一列</returns>
        public object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                PrepareCommand(conn, cmd, cmdType, cmdText, null, cmdParms);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();

                return val;
            }
        }

        /// <summary>
        /// 在事务中执行查询, 返回结果第一行第一列
        /// 
        /// 创建: 邱柯皓 2009/01/17
        /// </summary>
        /// <param name="trans">事务</param>
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>返回结果第一行第一列</returns>
        public object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(trans.Connection, cmd, cmdType, cmdText, trans, cmdParms);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();

            return val;
        }

        /// <summary>
        /// 获取数据统计
        /// 
        /// 创建: 邱柯皓 2009/01/17 19:26
        /// </summary>
        /// <param name="tblName">表名</param>
        /// <param name="condition">查询条件</param>
        /// <returns>返回数据统计</returns>
        public int GetCount(string tblName, string condition)
        {
            StringBuilder strSQL = new StringBuilder("SELECT COUNT(*) FROM [" + tblName + "]");

            if (!string.IsNullOrEmpty(condition))
                strSQL.Append(" WHERE " + condition);

            object count = ExecuteScalar(CommandType.Text, strSQL.ToString(), null);

            return int.Parse(count.ToString());
        }
        
        /// <summary>
        /// 分页获取数据
        /// 
        /// 创建: 邱柯皓 2009/01/18 23:38
        /// </summary>
        /// <param name="pageSize">每页显示数量</param>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="tblName">表名</param>
        /// <param name="fldName">排序字段名</param>
        /// <param name="condition">查询条件</param>
        /// <param name="sort">排序方式, true为正序, false为倒序</param>
        /// <returns>返回DataReader对象</returns>
        public SqlDataReader GetPager(int pageSize, int pageIndex, string tblName, string fldName, string condition, bool sort)
        {
        	string strSQL = GetPagerSQL(pageSize, pageIndex, tblName, fldName, condition, sort);
        	return ExecuteReader(CommandType.Text, strSQL, null);
        }
        #endregion

        #region 数据类型转换
        /// <summary>
        /// 转换为Int类型
        /// 
        /// 创建: 邱柯皓 2009/01/19 10:04
        /// </summary>
        /// <param name="obj">要转换的数据</param>
        /// <returns>返回Int类型数据</returns>
        public int GetInt(object obj)
        {
            if (!string.IsNullOrEmpty(obj.ToString()))
                return Convert.ToInt32(obj.ToString());

            return 0;
        }

        /// <summary>
        /// 转换为long类型
        /// 
        /// 创建: 邱柯皓 2009/01/19 10:05
        /// </summary>
        /// <param name="obj">要转换的数据</param>
        /// <returns>返回long类型数据</returns>
        public long GetLong(object obj)
        {
            if (!string.IsNullOrEmpty(obj.ToString()))
                return Convert.ToInt64(obj.ToString());

            return 0;
        }

        /// <summary>
        /// 转换为String类型
        /// 
        /// 创建: 邱柯皓 2009/01/19 10:06
        /// </summary>
        /// <param name="obj">要转换的数据</param>
        /// <returns>返回String类型数据</returns>
        public string GetString(object obj)
        {
            return obj.ToString();
        }

        /// <summary>
        /// 转换为Char类型
        /// 
        /// 创建: 邱柯皓 2009/01/19 10:07
        /// </summary>
        /// <param name="obj">要转换的数据</param>
        /// <returns>返回Char类型数据</returns>
        public char GetChar(object obj)
        {
            if (!string.IsNullOrEmpty(obj.ToString()))
                return Convert.ToChar(obj.ToString());

            return char.MinValue;
        }

        /// <summary>
        /// 转换为DateTime类型
        /// 
        /// 创建: 邱柯皓 2009/01/19 10:09
        /// </summary>
        /// <param name="obj">要转换的数据</param>
        /// <returns>返回DateTime类型数据</returns>
        public DateTime GetDateTime(object obj)
        {
            try
            {
                return Convert.ToDateTime(obj.ToString());
            }
            catch
            {
                return DateTime.MinValue;
            }
        }

        /// <summary>
        /// 转换为Bool类型
        /// 
        /// 创建: 邱柯皓 2009/01/19 22:18
        /// </summary>
        /// <param name="obj">要转换的数据</param>
        /// <returns>返回Bool类型数据</returns>
        public bool GetBool(object obj)
        {
            if (obj.ToString() == "1" || obj.ToString().ToLower() == "true")
                return true;

            return false;
        }
        #endregion

        #region 创建SqlParameter实例
        /// <summary>
        /// 创建输入SqlParameter实例
        /// 
        /// 创建: 邱柯皓 2009/01/19 10:21
        /// </summary>
        /// <param name="paraName">实例名</param>
        /// <param name="dbType">数据类型</param>
        /// <param name="size">大小</param>
        /// <param name="value">值</param>
        /// <returns>返回SqlParameter</returns>
        public SqlParameter CreateInSqlParameter(string paraName, DbType dbType, int size, object value)
        {
            return CreateSqlParameter(paraName, dbType, size, value, ParameterDirection.Input);
        }

        /// <summary>
        /// 创建输入SqlParameter实例
        /// 
        /// 创建: 邱柯皓 2009/01/19 10:22
        /// </summary>
        /// <param name="paraName">实例名</param>
        /// <param name="dbType">类型</param>
        /// <param name="value">值</param>
        /// <returns>返回SqlParameter</returns>
        public SqlParameter CreateInSqlParameter(string paraName, DbType dbType, object value)
        {
            return CreateSqlParameter(paraName, dbType, 0, value, ParameterDirection.Input);
        }

        /// <summary>
        /// 创建输出SqlParameter实例
        /// 
        /// 创建: 邱柯皓 2009/01/19 10:23
        /// </summary>
        /// <param name="paraName">实例名</param>
        /// <param name="dbType">类型</param>
        /// <param name="size">大小</param>
        /// <returns>返回SqlParameter</returns>
        public SqlParameter CreateOutSqlParameter(string paraName, DbType dbType, int size)
        {
            return CreateSqlParameter(paraName, dbType, size, null, ParameterDirection.Output);
        }

        /// <summary>
        /// 创建输出SqlParameter实例
        /// 
        /// 创建: 邱柯皓 2009/01/19 10:24
        /// </summary>
        /// <param name="paraName">实例名</param>
        /// <param name="dbType">类型</param>
        /// <returns>返回SqlParameter</returns>
        public SqlParameter CreateOutSqlParameter(string paraName, DbType dbType)
        {
            return CreateSqlParameter(paraName, dbType, 0, null, ParameterDirection.Output);
        }

        /// <summary>
        /// 创建返回SqlParameter实例
        /// 
        /// 创建: 邱柯皓 2009/01/19 10:25
        /// </summary>
        /// <param name="paraName">实例名</param>
        /// <param name="dbType">类型</param>
        /// <param name="size">大小</param>
        /// <returns>返回SqlParameter</returns>
        public SqlParameter CreateReturnSqlParameter(string paraName, DbType dbType, int size)
        {
            return CreateSqlParameter(paraName, dbType, size, null, ParameterDirection.ReturnValue);
        }

        /// <summary>
        /// 创建返回SqlParameter实例
        /// 
        /// 创建: 邱柯皓 2009/01/19 10:26
        /// </summary>
        /// <param name="paraName">实例名</param>
        /// <param name="dbType">类型</param>
        /// <returns>返回SqlParameter</returns>
        public SqlParameter CreateReturnSqlParameter(string paraName, DbType dbType)
        {
            return CreateSqlParameter(paraName, dbType, 0, null, ParameterDirection.ReturnValue);
        }
        #endregion
    }
}